--    Program: 		CAPPOEXT_LOCATION.sql
--    Description:	Extract PO Location information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
  l_count				   number;
  l_processed_count_1 	   number;
  l_rejected_count_1  	   number;
  l_processed_count_2	   number;
  l_rejected_count_2	   number;
  l_err_msg       		   varchar2(2000);
                    		
  l_utl_file_1  	       utl_file.file_type;
  l_utl_file_2 		       utl_file.file_type;
  l_filename_1 		       varchar2(255);
  l_filename_2 		       varchar2(255);
  l_pathname  		       varchar2(100);
                           
  l_ngo_sob			       varchar2(20);

  l_set_of_books_id		   number;
 
  cursor cur_location_b(P_SET_OF_BOOKS_NAME varchar2) is
	select hl.*
	from   HR_LOCATIONS_ALL hl, HR_ORGANIZATION_UNITS hou
    where  hl.business_group_id = hou.business_group_id
    AND    upper(hou.name) = 'BG_'||upper(substr(P_SET_OF_BOOKS_NAME, -4));
                                 
  cursor cur_location_tl(P_LOCATION_ID number) is  
    select *
    from   HR_LOCATIONS_ALL_TL 
    where  LOCATION_ID = P_LOCATION_ID;
  
begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPPO_LOCATION_B.dat';
  l_filename_2 := 'CAPPO_LOCATION_TL.dat';
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w');
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w');
  
  l_count := 0;
  l_processed_count_1 := 0;
  l_rejected_count_1  := 0;
  l_processed_count_2 := 0;
  l_rejected_count_2  := 0;  
                                         
  l_ngo_sob := '&1';

  begin
    select set_of_books_id
    into   l_set_of_books_id
    from   gl_sets_of_books
    where  upper(short_name) = upper(l_ngo_sob);     
  exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
  end;  

   for b in cur_location_b(l_ngo_sob) loop
          
     utl_file.put_line(l_utl_file_1,
       b.LOCATION_ID                || chr(9) ||
       b.LOCATION_CODE              || chr(9) ||
       b.BUSINESS_GROUP_ID          || chr(9) ||
       b.DESCRIPTION                || chr(9) ||
       b.SHIP_TO_LOCATION_ID        || chr(9) ||
       b.SHIP_TO_SITE_FLAG          || chr(9) ||
       b.RECEIVING_SITE_FLAG        || chr(9) ||
       b.BILL_TO_SITE_FLAG          || chr(9) ||
       b.IN_ORGANIZATION_FLAG       || chr(9) ||
       b.OFFICE_SITE_FLAG           || chr(9) ||
       b.DESIGNATED_RECEIVER_ID     || chr(9) ||
       b.INVENTORY_ORGANIZATION_ID  || chr(9) ||
       b.TAX_NAME                   || chr(9) ||
       b.INACTIVE_DATE              || chr(9) ||
       b.STYLE                      || chr(9) ||
       b.ADDRESS_LINE_1             || chr(9) ||
       b.ADDRESS_LINE_2             || chr(9) ||
       b.ADDRESS_LINE_3             || chr(9) ||
       b.TOWN_OR_CITY               || chr(9) ||
       b.COUNTRY                    || chr(9) ||
       b.POSTAL_CODE                || chr(9) ||
       b.REGION_1                   || chr(9) ||
       b.REGION_2                   || chr(9) ||
       b.REGION_3                   || chr(9) ||
       b.TELEPHONE_NUMBER_1         || chr(9) ||
       b.TELEPHONE_NUMBER_2         || chr(9) ||
       b.TELEPHONE_NUMBER_3         || chr(9) ||
       b.LOC_INFORMATION13          || chr(9) ||
       b.LOC_INFORMATION14          || chr(9) ||
       b.LOC_INFORMATION15          || chr(9) ||
       b.LOC_INFORMATION16          || chr(9) ||
       b.LOC_INFORMATION17          || chr(9) ||
       b.ATTRIBUTE_CATEGORY         || chr(9) ||
       b.ATTRIBUTE1                 || chr(9) ||
       b.ATTRIBUTE2                 || chr(9) ||
       b.ATTRIBUTE3                 || chr(9) ||
       b.ATTRIBUTE4                 || chr(9) ||
       b.ATTRIBUTE5                 || chr(9) ||
       b.ATTRIBUTE6                 || chr(9) ||
       b.ATTRIBUTE7                 || chr(9) ||
       b.ATTRIBUTE8                 || chr(9) ||
       b.ATTRIBUTE9                 || chr(9) ||
       b.ATTRIBUTE10                || chr(9) ||
       b.ATTRIBUTE11                || chr(9) ||
       b.ATTRIBUTE12                || chr(9) ||
       b.ATTRIBUTE13                || chr(9) ||
       b.ATTRIBUTE14                || chr(9) ||
       b.ATTRIBUTE15                || chr(9) ||
       b.ATTRIBUTE16                || chr(9) ||
       b.ATTRIBUTE17                || chr(9) ||
       b.ATTRIBUTE18                || chr(9) ||
       b.ATTRIBUTE19                || chr(9) ||
       b.ATTRIBUTE20                || chr(9) ||
       b.GLOBAL_ATTRIBUTE_CATEGORY  || chr(9) ||
       b.GLOBAL_ATTRIBUTE1          || chr(9) ||
       b.GLOBAL_ATTRIBUTE2          || chr(9) ||
       b.GLOBAL_ATTRIBUTE3          || chr(9) ||
       b.GLOBAL_ATTRIBUTE4          || chr(9) ||
       b.GLOBAL_ATTRIBUTE5          || chr(9) ||
       b.GLOBAL_ATTRIBUTE6          || chr(9) ||
       b.GLOBAL_ATTRIBUTE7          || chr(9) ||
       b.GLOBAL_ATTRIBUTE8          || chr(9) ||
       b.GLOBAL_ATTRIBUTE9          || chr(9) ||
       b.GLOBAL_ATTRIBUTE10         || chr(9) ||
       b.GLOBAL_ATTRIBUTE11         || chr(9) ||
       b.GLOBAL_ATTRIBUTE12         || chr(9) ||
       b.GLOBAL_ATTRIBUTE13         || chr(9) ||
       b.GLOBAL_ATTRIBUTE14         || chr(9) ||
       b.GLOBAL_ATTRIBUTE15         || chr(9) ||
       b.GLOBAL_ATTRIBUTE16         || chr(9) ||
       b.GLOBAL_ATTRIBUTE17         || chr(9) ||
       b.GLOBAL_ATTRIBUTE18         || chr(9) ||
       b.GLOBAL_ATTRIBUTE19         || chr(9) ||
       b.GLOBAL_ATTRIBUTE20         || chr(9) ||
       b.LAST_UPDATE_DATE           || chr(9) ||
       b.LAST_UPDATED_BY            || chr(9) ||
       b.LAST_UPDATE_LOGIN          || chr(9) ||
       b.CREATED_BY                 || chr(9) ||
       b.CREATION_DATE              || chr(9) ||
       b.ENTERED_BY                 || chr(9) ||
       b.TP_HEADER_ID               || chr(9) ||
       b.ECE_TP_LOCATION_CODE       || chr(9) ||
       b.OBJECT_VERSION_NUMBER      || chr(9) ||
       --b.GEOMETRY                   || chr(9) ||
       b.LOC_INFORMATION18          || chr(9) ||
       b.LOC_INFORMATION19          || chr(9) ||
       b.LOC_INFORMATION20          || chr(9) ||
       b.DERIVED_LOCALE   
     );
   
     for t in cur_location_tl(b.location_id) loop
        utl_file.put_line(l_utl_file_2,
          t.LOCATION_ID        || chr(9) ||
          t.LANGUAGE           || chr(9) ||
          t.SOURCE_LANG        || chr(9) ||
          t.LOCATION_CODE      || chr(9) ||
          t.DESCRIPTION        || chr(9) ||
          t.LAST_UPDATE_DATE   || chr(9) ||
          t.LAST_UPDATED_BY    || chr(9) ||
          t.LAST_UPDATE_LOGIN  || chr(9) ||
          t.CREATED_BY         || chr(9) ||
          t.CREATION_DATE
        );
        
        l_processed_count_2 := l_processed_count_2 + 1;     
     end loop;
     
     l_processed_count_1 := l_processed_count_1 + 1;        
   end loop;
   
   l_err_msg := '';
   if l_processed_count_1 = 0 then   
     l_rejected_count_1 := l_rejected_count_1 + 1;
     l_err_msg := l_ngo_sob || ': Location not found';
   end if; 
      
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ( 'CAPPOEXT_LOCATION_B', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );            
   
   l_err_msg := '';
   if l_processed_count_2 = 0 then   
     l_rejected_count_2 := l_rejected_count_2 + 1;
     l_err_msg := l_ngo_sob || ': Location not found';
   end if;      
           
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ( 'CAPPOEXT_LOCATION_TL', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );      
      
   commit;
   
   utl_file.fclose(l_utl_file_1);
   utl_file.fclose(l_utl_file_2);
exception 
   when others then
     dbms_output.put_line(sqlerrm);
     rollback;
end;
/


--exit;
